package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pojo.Customer;
import util.DBUtil2;

public class CustomerDaoImpl implements CustomerDao {
	
	private int getIdByName(String name){
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "select id from customers where cname=?";
		try {
			connection = DBUtil2.createConnection();
			pst=connection.prepareStatement(sql);
			pst.setString(1, name);
			ResultSet rs = null;
			rs=pst.executeQuery();
			int id=0;
			while(rs.next()){
				id = rs.getInt("id");
			}
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil2.close(null, pst, null);
		}
	}
	
	private int addHobby(int user_id,String[] hobbys){
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "insert into cus_hobby(user_id,hobby_id) values(?,?)";
		int count=0;
		connection = DBUtil2.createConnection();
		for(int i=0; i<hobbys.length; i++){
			System.out.println(hobbys[i]);
			try {
				pst=connection.prepareStatement(sql);
				pst.setInt(1, user_id);
				pst.setInt(2, Integer.parseInt(hobbys[i]));
				int n=pst.executeUpdate();
				count+=n;
			} catch (SQLException e) {
				e.printStackTrace();
			} finally{
				DBUtil2.close(null, pst, null);
			}
		}
		return count;
	}

	private int deleteHobby(int user_id){
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "delete from cus_hobby where user_id=?";
		connection = DBUtil2.createConnection();
		int m=0;
		try {
			pst=connection.prepareStatement(sql);
			pst.setInt(1, user_id);
			m=pst.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil2.close(null, pst, null);
		}
	}
	
	
	@Override
	public int addCustomer(Customer c) {
		Connection connection = null;
		PreparedStatement pst = null;
		String sql = "insert into customers(id,cname,sex,sal,birth) values(cusseq.nextval,?,?,?,?)";
		int m=0;
		try {
			connection = DBUtil2.createConnection();
			pst=connection.prepareStatement(sql);
			pst.setString(1, c.getCname());
			pst.setBoolean(2, c.isSex());
			pst.setDouble(3, c.getSal());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			m=pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil2.close(null, pst, null);
		}
		
		int id = getIdByName(c.getCname());
		if(id != 0){
			System.out.println(c.getHobby());
			int n = addHobby(id, c.getHobby());
			if(n==c.getHobby().length){
				return 1;
			}else{
				return 0;
			}
		}else{
			return 0;
		}
	}
	
	@Override
	public List<Customer> findAllCustomers() {
		List<Customer> list = new ArrayList<Customer>();
		
		String sql="select * from customers";
		Connection connection = null;
		PreparedStatement pst=null;
		try {
			connection = DBUtil2.createConnection();
			pst = connection.prepareStatement(sql);
			ResultSet rs = null;
			rs=pst.executeQuery();
			
			while(rs.next()){
				Customer customer = new Customer();
				customer.setId(rs.getInt("id"));
				customer.setCname(rs.getString("cname"));
				customer.setSex(rs.getBoolean("sex"));
				customer.setSal(rs.getDouble("sal"));
				customer.setBirth(rs.getDate("birth"));
				list.add(customer);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally{
			DBUtil2.close(connection, pst, null);
		}
	}

	@Override
	public int updateCustomer(Customer c) {
		String sql="update customers set cname=?,sex=?,sal=?,birth=? where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil2.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, c.getCname());
			pst.setBoolean(2, c.isSex());
			pst.setDouble(3, c.getSal());
			pst.setDate(4, new java.sql.Date(c.getBirth().getTime()));
			pst.setInt(5, c.getId());
			m=pst.executeUpdate();
			System.out.println("update..."+m);
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil2.close(null, pst, null);
		}
		
		int x,y;
		x=deleteHobby(c.getId());
		y=addHobby(c.getId(), c.getHobby());
		
		if(x!=0 && y!=0){
			return 1;
		}else{
			return 0;
		}
	}

	@Override
	public int deleteCustomer(Integer id) {
		String sql="delete from customers where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil2.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			m=pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil2.close(null, pst, null);
		}
		
		int n = deleteHobby(id);
		if(n!=0){
			return 1;
		}else{
			return 0;
		}
		
	}

	@Override
	public String[] getCustomerHobby(Integer id) {
		List<String> list = new ArrayList<String>();
		String sql="select * from cus_hobby where user_id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		try {
			connection = DBUtil2.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			ResultSet rs = null;
			rs=pst.executeQuery();
			
			while(rs.next()){
				String s = rs.getString("hobby_id");
				list.add(s);
			}
			int length = list.size();
			String[] hobby = new String[length];
			for(int i=0; i<length; i++){
				hobby[i] = list.get(i);
			}
			return hobby;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally{
			DBUtil2.close(connection, pst, null);
		}
	}

}
